﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Xml;
using System.Xml.Linq;
using System.Xml.Xsl;
using Cog.Model;
using Cog.Properties;
using ICSharpCode.SharpZipLib.Zip;

//Author: Joseph De Guzman
//Email: tuldoklambat@gmail.com

namespace Cog
{
    internal class CodeGen
    {
        private static CogDataSet _cogData;
        private DataSet _targetDataSet;

        private readonly List<TemplateInfo> _templates;

        private string _projectTemplate = "Cog.Template.ProjectTemplate.zip";

        private const string SolutionItemsPlaceHolder = "$$$$COGSOLUTIONITEMS$$$$";
        private const string ClassItemsPlaceHolder = "$$$$COGCLASSES$$$$";
        private const string DataSetItemPlaceHolder = "$$$$COGDATASET$$$$";
        private const string ProjectNamePlaceHolder = "$$$$Template$$$$";
        private const string SchemaPlaceHolder = "$$$$SCHEMA$$$$";
        private const string NameSpacePlaceHolder = "$$$$NAMESPACE$$$$";
        private const string ConnectionStringPlaceHolder = "$$$$CONNECTIONSTRING$$$$";

        public CodeGen(string nameSpace, string schema, string connectionString, string dest, bool generateRelationships, string classPrefix)
        {
            NameSpace = nameSpace;
            Schema = schema;
            ConnectionString = connectionString;
            Dest = dest;
            ClassPrefix = classPrefix;

            _templates = new List<TemplateInfo>
                             {
                                 new TemplateInfo
                                     {
                                         Template = "Cog.Template.SelectTemplate.xslt",
                                         FileName = string.Format("{0}\\Database\\SelectScripts.sql", Path.Combine(Dest, NameSpace)),
                                         Type = "Database"
                                     },
                                 new TemplateInfo
                                     {
                                         Template = "Cog.Template.InsertTemplate.xslt",
                                         FileName = string.Format("{0}\\Database\\InsertScripts.sql", Path.Combine(Dest, NameSpace)),
                                         Type = "Database"
                                     },
                                 new TemplateInfo
                                     {
                                         Template = "Cog.Template.UpdateTemplate.xslt",
                                         FileName = string.Format("{0}\\Database\\UpdateScripts.sql", Path.Combine(Dest, NameSpace)),
                                         Type = "Database"
                                     },
                                 new TemplateInfo
                                     {
                                         Template = "Cog.Template.DeleteTemplate.xslt",
                                         FileName = string.Format("{0}\\Database\\DeleteScripts.sql", Path.Combine(Dest, NameSpace)),
                                         Type = "Database"
                                     },
                                 new TemplateInfo
                                     {
                                         Template = "Cog.Template.LoadByTemplate.xslt",
                                         FileName = string.Format("{0}\\Database\\LoadByScripts.sql", Path.Combine(Dest, NameSpace)),
                                         Type = "Database"
                                     },
                                 new TemplateInfo
                                     {
                                         Template = "Cog.Template.DataSetTemplate.xslt",
                                         FileName = string.Format("{0}\\{1}\\{2}DataSetExtended.cs", Dest, NameSpace, "{0}"),
                                         Type = "DataSet"
                                     },
                                 new TemplateInfo
                                     {
                                         Template =
                                             generateRelationships
                                                 ? "Cog.Template.DataTableWithChildTemplate.xslt"
                                                 : "Cog.Template.DataTableTemplate.xslt",
                                         FileName = string.Format("{0}\\{1}\\{2}DataTable.cs", Dest, NameSpace,"{0}"),
                                         Type = "DataTable"
                                     },
                                 new TemplateInfo
                                     {
                                         Template = "Cog.Template.DataViewTemplate.xslt",
                                         FileName = string.Format("{0}\\{1}\\{2}Views.cs", Dest, NameSpace,"{0}"),
                                         Type = "DataView"
                                     }
                             };
        }

        private string NameSpace { get; set; }
        private string Schema { get; set; }
        private string ConnectionString { get; set; }
        private string Dest { get; set; }
        private string ClassPrefix { get; set; }

        private SqlDataReader ExecuteQuery(string commandText)
        {
            var con = new SqlConnection(ConnectionString);
            con.Open();
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = commandText;
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

        private CogDataSet CogData
        {
            get
            {
                if(_cogData == null)
                {
                    _cogData = new CogDataSet();
                    _cogData.TableInfo.Load(ExecuteQuery(string.Format(Settings.Default.TableQuery, Schema)));
                    _cogData.ColumnInfo.Load(ExecuteQuery(string.Format(Settings.Default.ColumnQuery, Schema)));
                    _cogData.ViewInfo.Load(ExecuteQuery(string.Format(Settings.Default.ViewQuery, Schema)));
                    
                }
                return _cogData;
            }
        }

        private DataSet TargetDataSet
        {
            get
            {
                if(_targetDataSet == null)
                {
                    _targetDataSet = new DataSet(string.Format("{0}DataSet", ClassPrefix));
                    foreach (CogDataSet.TableInfoRow row in CogData.TableInfo.Rows)
                    {
                        var query = string.Format("SELECT * FROM [{0}].[{1}] WHERE 1=2", row.Schema, row._Table);
                        var table = new DataTable(row.ManagedTable);
                        table.Load(ExecuteQuery(query));

                        var pk = from c1 in CogData.ColumnInfo.Rows.Cast<CogDataSet.ColumnInfoRow>()
                                 from c2 in table.Columns.Cast<DataColumn>()
                                 where c1.Column == c2.ColumnName && c1.IsPrimaryKey && c1._Table == c2.Table.TableName
                                 select c2;

                        table.PrimaryKey = pk.ToArray();

                        _targetDataSet.Tables.Add(table);
                    }

                    foreach (CogDataSet.ViewInfoRow row in CogData.ViewInfo.Rows)
                    {
                        var query = string.Format("SELECT * FROM [{0}].[{1}] WHERE 1=2", row.Schema, row._Table);
                        var table = new DataTable(row.ManagedTable);
                        table.Load(ExecuteQuery(query));

                        _targetDataSet.Tables.Add(table);
                    }
                }
                return _targetDataSet;
            }
        }

        private string GenerateMetadata()
        {
            foreach (var row in CogData.TableInfo)
            {
                row.Namespace = NameSpace;
                row.ManagedTable = row._Table.Replace(" ", "");
                row.ClassPrefix = ClassPrefix;
            }

            foreach (var row in CogData.ViewInfo)
            {
                row.Namespace = NameSpace;
                row.ManagedTable = row._Table.Replace(" ", "");
                row.ClassPrefix = ClassPrefix;
            }

            foreach (var row in CogData.ColumnInfo)
            {
                row.Table2 = row._Table.Replace(" ", "");
                row.ManagedTable = (char.ToLower(row._Table[0]) + row._Table.Substring(1)).Replace(" ","");
                row.ManagedColumn = (char.ToLower(row.Column[0]) + row.Column.Substring(1)).Replace(" ","");
                row.ManagedDataType = TargetDataSet.Tables[row.Table2].Columns[row.Column].DataType.Name;
            }

            return CogData.GetXml();
        }

        public static void CopyStream(Stream input, Stream output)
        {
            var buffer = new byte[8192];

            int bytesRead;
            while ((bytesRead = input.Read(buffer, 0, buffer.Length)) > 0)
            {
                output.Write(buffer, 0, bytesRead);
            }
        }

        private static string Transform(string xml, string template, Dictionary<string, string> templateParams)
        {
            XsltArgumentList xsltArguments = null;
            if(templateParams != null)
            {
                xsltArguments = new XsltArgumentList();
                foreach (var templateParam in templateParams)
                {
                    xsltArguments.AddParam(templateParam.Key,string.Empty, templateParam.Value);
                }
            }
            var assembly = Assembly.GetExecutingAssembly();
            var result = new StringBuilder();
            var metaDataDoc = XDocument.Parse(xml);
            using (var writer = new StringWriter(result))
            {
                var transform = new XslCompiledTransform();
                using (var resource = assembly.GetManifestResourceStream(template))
                {
                    if (resource == null) return null;
                    transform.Load(XmlReader.Create(resource));
                    transform.Transform(metaDataDoc.CreateReader(), xsltArguments, writer);
                }
            }
            return result.ToString();
        }


        private void WriteText(string filePath, string content)
        {
            var destFileInfo = new FileInfo(filePath);
            if (destFileInfo.Directory != null) destFileInfo.Directory.Create();
            File.WriteAllText(filePath, content);
        }

        public void Build()
        {
            var tempPath = Path.Combine(Path.GetTempPath(), "Cog");

            Console.WriteLine("Fetching table metadata...");
            var metaData = GenerateMetadata();

            var assembly = Assembly.GetExecutingAssembly();
            assembly.GetManifestResourceStream(_projectTemplate);

            //unzip project template
            Console.WriteLine("Creating project...");
            var projectTemplate = new FastZip {CreateEmptyDirectories = true};
            projectTemplate.ExtractZip(assembly.GetManifestResourceStream(_projectTemplate), tempPath,
                                       FastZip.Overwrite.Always, null, "", "", false, true);

            //replace placeholders in the project template
            foreach (var file in Directory.GetFiles(tempPath, "*.*", SearchOption.AllDirectories))
            {
                File.SetAttributes(file, FileAttributes.Archive);
                var content = File.ReadAllText(file);
                content = content.Replace(ProjectNamePlaceHolder, NameSpace);
                content = content.Replace(NameSpacePlaceHolder, NameSpace);
                content = content.Replace(ConnectionStringPlaceHolder, ConnectionString);
                content = content.Replace(SchemaPlaceHolder, ClassPrefix);
                var destFile = file.Replace(ProjectNamePlaceHolder, NameSpace);
                destFile = Path.Combine(Dest, destFile.Replace(tempPath, string.Empty).Trim('\\'));
                WriteText(destFile, content);
            }

            //generate database scripts
            Console.WriteLine("Creating source codes...");
            var solutionItems = string.Empty;
            var classItems = string.Empty;
            foreach (var template in _templates)
            {
                string result;
                switch (template.Type)
                {
                    case "Database":
                        if (CogData.TableInfo.Rows.Count == 0 && CogData.ColumnInfo.Rows.Count == 0) break;
                        result = Transform(metaData, template.Template, null);
                        if (string.IsNullOrEmpty(result)) continue;
                        WriteText(template.FileName, result);
                        solutionItems += string.Format("\t\t{1}\\Database\\{0} = {1}\\Database\\{0}\r\n", Path.GetFileName(template.FileName), NameSpace);
                        break;
                    case "DataSet":
                        if (CogData.TableInfo.Rows.Count == 0 && CogData.ColumnInfo.Rows.Count == 0) break;
                        result = Transform(metaData, template.Template, null);
                        if (string.IsNullOrEmpty(result)) continue;
                        WriteText(string.Format(template.FileName, ClassPrefix), result);
                        classItems += string.Format("\t<Compile Include=\"{0}\"/>\r\n", string.Format(Path.GetFileName(template.FileName), ClassPrefix));
                        break;
                    case "DataTable":
                        if (CogData.TableInfo.Rows.Count == 0 && CogData.ColumnInfo.Rows.Count == 0) break;
                        foreach (CogDataSet.TableInfoRow row in CogData.TableInfo.Rows)
                        {
                            var classPath = string.Format(template.FileName, row.ManagedTable);
                            result = Transform(metaData, string.Format(template.Template, row._Table),
                                               new Dictionary<string, string> {{"tableName", row._Table}});
                            if (string.IsNullOrEmpty(result)) continue;
                            WriteText(classPath , result);
                            classItems += string.Format("\t<Compile Include=\"{0}\"/>\r\n", Path.GetFileName(classPath));
                        }
                        break;
                    case "DataView":
                        if (CogData.ViewInfo.Rows.Count == 0) break;
                        result = Transform(metaData, template.Template, null);
                        if (string.IsNullOrEmpty(result)) continue;
                        WriteText(string.Format(template.FileName, ClassPrefix), result);
                        classItems += string.Format("\t<Compile Include=\"{0}\"/>\r\n", string.Format(Path.GetFileName(template.FileName), ClassPrefix));
                        break;
                }
            }

            //add dataset
            Console.WriteLine("Creating datasets...");
            TargetDataSet.WriteXmlSchema(Path.Combine(Dest, string.Format("{0}\\{1}DataSet.xsd", NameSpace, ClassPrefix)));
            TargetDataSet.WriteXmlSchema(Path.Combine(Dest, string.Format("{0}\\{1}DataSet.Designer.xsd", NameSpace, ClassPrefix)));
            classItems +=
                string.Format(
                    "<Compile Include=\"{0}DataSet.Designer.cs\">\r\n\t<AutoGen>True</AutoGen>\r\n\t<DesignTime>True</DesignTime>\r\n\t<DependentUpon>{0}DataSet.xsd</DependentUpon>\r\n</Compile>",
                    ClassPrefix);
            var dataSetItem = string.Format("\t<None Include=\"{0}\">\r\n<SubType>Designer</SubType>\r\n<Generator>MSDataSetGenerator</Generator>\r\n\t<LastGenOutput>{1}DataSet.Designer.cs</LastGenOutput>\r\n</None>", string.Format("{0}DataSet.xsd", ClassPrefix),ClassPrefix);
            
            //generate dataset classes from xsd.exe
            var processInfo = new ProcessStartInfo(Path.Combine(Path.GetDirectoryName(assembly.Location), "xsd.exe"))
                                  {
                                      Arguments = string.Format("{0} /dataset /eld /out:{1} /namespace:{2}",
                                                                Path.Combine(Dest,
                                                                             string.Format(
                                                                                 "{0}\\{1}DataSet.Designer.xsd", NameSpace, ClassPrefix)),
                                                                string.Format("{0}\\{1}", Dest, NameSpace), NameSpace),
                                      WindowStyle = ProcessWindowStyle.Hidden,
                                      RedirectStandardError = true,
                                      RedirectStandardOutput = true,
                                      UseShellExecute = false
                                  };

            Process.Start(processInfo).WaitForExit();

            File.Delete(Path.Combine(Dest, string.Format("{0}\\{1}DataSet.Designer.xsd", NameSpace, ClassPrefix)));

            //add solution items and classes to project
            Console.WriteLine("Creating solution...");
            var solutionPath = Path.Combine(Dest, string.Format("{0}Master.sln", NameSpace));
            var projectPath = Path.Combine(Dest, string.Format("{0}\\{0}.csproj", NameSpace));
            var solutionContent = File.ReadAllText(solutionPath);
            var projectContent = File.ReadAllText(projectPath);
            WriteText(solutionPath, solutionContent.Replace(SolutionItemsPlaceHolder, solutionItems));
            WriteText(projectPath,
                      projectContent.Replace(ClassItemsPlaceHolder, classItems).Replace(DataSetItemPlaceHolder,
                                                                                        dataSetItem));

            //dump xml and xsly to debug folder for inspection
            var debugPath = Path.Combine(Dest, "Debug");
            WriteText(Path.Combine(debugPath, string.Format("{0}MetaData.xml", ClassPrefix)), metaData);
            foreach (var template in _templates)
            {
                using(var input = assembly.GetManifestResourceStream(template.Template))
                    using(var output = File.Create(Path.Combine(debugPath, template.Template)))
                    {
                        CopyStream(input, output);
                    }
            }

            Console.WriteLine("Done.");

            Directory.Delete(tempPath, true);
        }

        private class TemplateInfo
        {
            public string Template { get; set; }
            public string FileName { get; set; }
            public string Type { get; set; }
        }
    }
}
